﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MYSQL_db.Model;
using System.Data;
using MySql.Data.MySqlClient;

namespace MYSQL_db.DAL
{
    class CardDAL
    {
        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性
        private static Card ToCard(DataRow row)
        {   //把从数据库中查询到的行中字段值，封装到对象中
            Card card = new Card();
            card.Id = (long)row["Id"];
            card.MemCardID = (string)row["MemCardID"];
            card.ISN = (string)row["ISN"];
            card.Balance = (float)row["Balance"];
            card.Date = (DateTime)row["Date"];
            card.Status = (int)row["Status"];
            return card;
        }

        //通过MemCardID查询返回Card对象
        public static Card GetById(string MemCardID)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Card where MemCardID=@MemCardID",
                new MySqlParameter("@MemCardID", MemCardID));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0]; //获取返回表中的第一行数据
                return ToCard(row);    //将返回行中数据封装到Customer对象中
            }
        }


        //通过卡序列号ISN查询返回Card对象
        public static Card GetByISN(string ISN)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Card where ISN=@ISN",
                new MySqlParameter("@ISN", ISN));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0]; //获取返回表中的第一行数据
                return ToCard(row);    //将返回行中数据封装到Customer对象中
            }
        }


        //通过卡号删除卡
        public static int DeleteById(string MemCardID)
        {
            return
            SqlHelper.ExecuteNonQuery("delete from T_Card where MemCardID=@MemCardID",
                new MySqlParameter("@MemCardID", MemCardID));
        }
        //Customer对象作为参数，函数中取出属性值插入数据库
        public static int Insert(Card card)   //返回值是sql操作影响到的行数
        {
            return
            SqlHelper.ExecuteNonQuery(@"INSERT INTO T_Card
                       (MemCardID
                       ,ISN
                       ,Balance
                       ,Date
                        ,Status
                        )
                 Values(@MemCardID, @ISN,@Balance,@Date,@Status)", new MySqlParameter("@MemCardID", card.MemCardID),
                                               new MySqlParameter("@ISN", card.ISN),
                                               new MySqlParameter("@Balance", card.Balance),
                                               new MySqlParameter("@Date", card.Date),
                                                   new MySqlParameter("@Status", card.Status)                
                                               );
        }

        //传入一个Card对象，提取对象属性，修改数据库值
        public static int Update(Card card)
        {
            return
            SqlHelper.ExecuteNonQuery(@"UPDATE T_Card
                       SET MemCardID = @MemCardID
                          ,ISN= @ISN
                          ,Balance = @Balance
                          ,Date = @Date
                          ,Status = @Status
                            WHERE MemCardID=@MemCardID",
                                  new MySqlParameter("@MemCardID", card.MemCardID),
                                  new MySqlParameter("@ISN", card.ISN),
                                  new MySqlParameter("@Balance", card.Balance),
                                  new MySqlParameter("@Date", card.Date),
                                   new MySqlParameter("@Status", card.Status)
                               );
        }
        //无参数查询数据库，返回Card对象数组
        public static Card[] GetAll()
        {

            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Card");
            Card[] card = new Card[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i]; //循环取出表中每一行

                card[i] = ToCard(row); //把取到的每一行加入对象数组
            }
            return card; //返回对象数组
        }
        //通过ISN查询数据库，返回card对象数组
        //无参数查询数据库，返回Card对象数组
        public static Card[] GetAllByISN(string ISN)
        {
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Card where ISN=@ISN",
                new MySqlParameter("@ISN", ISN));
            Card[] card = new Card[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i]; //循环取出表中每一行

                card[i] = ToCard(row); //把取到的每一行加入对象数组
            }
            return card; //返回对象数组
        }


    }
}
